#include "mydbexec.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QVariant>
#include <QJsonObject>
#include <QJsonArray>
#include <QDebug>
#include <QDateTime>

//#define DB_IP "172.16.10.198"
//#define DB_IP "192.168.43.113"
#define DB_IP "192.168.3.193"
#define DB_PORT 13306
#define DB_USERNAME "root"
#define DB_PASSWORD "123456"
#define DB_NAME "EnvMessage"
MyDBExec::MyDBExec(QString connectName,QObject *parent) : QObject(parent)
{
    db = new QSqlDatabase(QSqlDatabase::addDatabase("QMYSQL",connectName));
     connectToDB();
}
bool MyDBExec::getFreeState()
{
return freeState;
}
void MyDBExec::setFreeState(bool state)
{
    freeState = state;
}
bool MyDBExec::userLogin(QString username, QString password)
{

QSqlQuery query(*db);
QString cmd;
cmd = QString ("SELECT `password` FROM "
               "`user_info` WHERE "
               "`username`='%1';").arg(username);
bool ok = query.exec(cmd);

if(!ok)
{
    if(query.lastError().type() == QSqlError::ConnectionError)
    {connectToDB();}
    return false;
}
if(!query.next())
{
    return false;
}
return query.value("password").toString() == password ? true : false;
}



bool MyDBExec::receiveMessage(int boardid, double tem, double
                               hum1, double light, double press, double hum2, bool water1,
                              bool water2)
{
    QSqlQuery query(*db);
    QString cmd;
    cmd = QString("INSERT INTO `Message_info` "
                  "(`boardid`,`tem`,`hum1`,`light`,`press`,`hum2`,`water1`,`water2`,`time`) "
                  "VALUES(%1,%2,%3,%4,%5,%6,%7,%8,'%9');")
            .arg(boardid).arg(tem).arg(hum1).arg(light).arg(press).arg(hum2)
            .arg(water1 == true? 1 : 0).arg(water2 == true? 1 : 0)
            .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));
    qDebug() << cmd;//插入语句
   bool ok=query.exec(cmd);
     if(!ok)
         return false;
     return true;

}

bool MyDBExec::deviceLogin(QString index, QString password)
{
    QSqlQuery query(*db);
    QString cmd;
    cmd = QString ("SELECT `password` FROM "
                   "`device_info` WHERE "
                   "`device_id`='%1';").arg(index);
    bool ok = query.exec(cmd);

    if(!ok)
    {
        if(query.lastError().type() == QSqlError::ConnectionError)
        {connectToDB();}
        return false;
    }
    if(!query.next())
    {
        return false;
    }
    return query.value("password").toString() == password ? true : false;
}

bool MyDBExec::searchTodayMessage(QString device_id , QJsonArray &arr)
{
    QSqlQuery query(*db);
    QString cmd;
    cmd = QString("SELECT * FROM "
                  " `Message_info` ;");
    bool ok = query.exec(cmd);
    if(!ok)
    {
        if(query.lastError().type() == QSqlError::ConnectionError)
        {connectToDB();}
        return false;
    }
    QDateTime now=QDateTime::currentDateTime();
    now =now.addDays(-1);
   //qDebug() << now.toString("yyyy-MM-dd hh:mm:ss");
    while (query.next())
    {

      QDateTime time =  query.value("time").toDateTime();
      //qDebug() << time;
      if(time < now)
          continue;
QJsonObject obj;
obj.insert("boardid",query.value("boardid").toInt());
obj.insert("tem",query.value("tem").toDouble());
obj.insert("hum1",query.value("hum1").toDouble());
obj.insert("light",query.value("light").toDouble());
obj.insert("press",query.value("press").toDouble());
obj.insert("hum2",query.value("hum2").toDouble());
obj.insert("water1",query.value("water1").toBool());
obj.insert("water2",query.value("water2").toBool());
obj.insert("time",query.value("time").toString());
  arr.append(obj);

    }

//qDebug() << "Message arr==" << arr;


}

bool MyDBExec::userGetMyDeviceName(QString username, QJsonArray &arr)
{
    QSqlQuery query(*db);
    QString cmd;
    cmd = QString("SELECT * FROM "
                  " `user_device` WHERE "
                  " `username` = '%1';").arg(username);
    bool ok = query.exec(cmd);
    if(!ok)
    {
        if(query.lastError().type() == QSqlError::ConnectionError)
        {connectToDB();}
        return false;
    }
    while(query.next())
    {
  QJsonObject obj;
  obj.insert("device_id",query.value("device_id").toString());
  arr.append(obj);
    }
    return true;
}


void MyDBExec::connectToDB(QString userName)
{
    if(db->isOpen())
        db->close();
        db->setHostName(DB_IP);
        db->setPort(DB_PORT);
        db->setUserName(DB_USERNAME);
        db->setPassword(DB_PASSWORD);
        db->setDatabaseName(DB_NAME);
        db->open();
}
